﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Text.RegularExpressions;

namespace UsersDAL
{
    public class sqlHelper
    {
        private static string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
        //普通
        public static int ExecuteNonQuery(string sql)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    return cmd.ExecuteNonQuery();
                }
            }
        }
        //params  部分传参
        public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteNonQuery();
                }
            }
        }
        public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteScalar();
                }
            }
        }
        //小规模的sql ，在本机读取
        public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;

                    cmd.Parameters.AddRange(parameters);
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataSet dataset = new DataSet();
                    adapter.Fill(dataset);
                    return dataset.Tables[0];

                }
            }
        }
        /// <summary>
        /// 分页查询，（存储过程）
        /// </summary>
        /// <param name="nowpage">当前页码</param>
        /// <param name="pagesize">每页条数</param>
        /// <param name="countrow">总行数</param>
        /// <param name="countpage">总页数</param>
        /// <returns></returns>
        //public static DataTable GetPageByPro(int pageIndex, int pageSize, out int rowCount, out int pageCount)
        //{
        //    rowCount = 1;
        //    pageCount = 1;
        //    SqlParameter[] parameters = {
        //        new SqlParameter("@PageIndex", SqlDbType.Int,4),
        //        new SqlParameter("@PageSize", SqlDbType.Int,4),
        //        new SqlParameter("@RowCount", SqlDbType.Int,4),
        //        new SqlParameter("@PageCount", SqlDbType.Int,4)};
        //    parameters[0].Value = pageIndex;
        //    parameters[1].Value = pageSize;
        //    parameters[2].Value = rowCount;
        //    parameters[2].Direction = ParameterDirection.Output;
        //    parameters[3].Value = pageCount;
        //    parameters[3].Direction = ParameterDirection.Output;
        //    SqlCommand cmd = new SqlCommand();
        //    SqlConnection conn = new SqlConnection(connStr);
        //    cmd.Connection = conn;
        //    cmd.CommandText = "GetPageDataOutRowPageCount";
        //    cmd.CommandType = CommandType.StoredProcedure;
        //    cmd.Parameters.AddRange(parameters);
        //    SqlDataAdapter da = new SqlDataAdapter(cmd);
        //    DataTable dt = new DataTable();
        //    da.Fill(dt);
        //    rowCount = Convert.ToInt32(parameters[2].Value);
        //    pageCount = Convert.ToInt32(parameters[3].Value);
        //    return dt;
        //}
        public static DataTable GetPageByPro(int nowpage, int pagesize, out int countrow, out int countpage)
        {
            countrow = 1;
            countpage = 1;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    SqlParameter[] para ={new SqlParameter("@PageIndex",nowpage)
                                        ,new SqlParameter("@PageSize",pagesize)
                                        ,new SqlParameter("@RowCount",SqlDbType.Int,4)
                                        ,new SqlParameter("@PageCount",SqlDbType.Int,4)};
                    para[2].Value = countrow;
                    para[3].Value = countpage;
                    para[2].Direction = ParameterDirection.Output;
                    para[3].Direction = ParameterDirection.Output;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "GetPageDataOutRowPageCount";
                    cmd.Parameters.AddRange(para);
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataSet dataset = new DataSet();
                    adapter.Fill(dataset);
                    countrow = Convert.ToInt32(para[2].Value);
                    countpage = Convert.ToInt32(para[3].Value);
                    return dataset.Tables[0];
                }
            }
        }
        public static DataTable GetPageByPro(int nowpage, int pagesize, out int countrow, out int countpage, string SqlPro)
        {
            countrow = 1;
            countpage = 1;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    SqlParameter[] para ={new SqlParameter("@PageIndex",nowpage)
                                        ,new SqlParameter("@PageSize",pagesize)
                                        ,new SqlParameter("@RowCount",SqlDbType.Int,4)
                                        ,new SqlParameter("@PageCount",SqlDbType.Int,4)};
                    para[2].Value = countrow;
                    para[3].Value = countpage;
                    para[2].Direction = ParameterDirection.Output;
                    para[3].Direction = ParameterDirection.Output;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = SqlPro;
                    cmd.Parameters.AddRange(para);
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataSet dataset = new DataSet();
                    adapter.Fill(dataset);
                    countrow = Convert.ToInt32(para[2].Value);
                    countpage = Convert.ToInt32(para[3].Value);
                    return dataset.Tables[0];
                }
            }
        }
        public static object GetDbvalue(object obj)
        {
            if (obj == null)
            {
                return DBNull.Value;
            }
            else
            {
                return obj;
            }
        }

        public static object GetToDbvalue(object obj)
        {
            if (obj == DBNull.Value)
            {
                return null;
            }
            else
            {
                return obj;
            }
        }

        /// <summary>
        /// 获取两个字符串之间的字符
        /// </summary>
        /// <returns></returns>
        public static string GetValueAnd(string strStart, string strEnd, string text)
        {
            if (string.IsNullOrEmpty(text))
                return "";
            string regex = @"^.*" + strStart + "(?<content>.+?)" + strEnd + ".*$";
            Regex rgClass = new Regex(regex, RegexOptions.Singleline);
            Match match = rgClass.Match(text);
            return match.Groups["content"].Value;
        }
    }
}
